using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Invoices : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public bool PayForInvoice(string sInvoiceCode, bool isPayed, string sPayedDate)
		{
			string cmdText;
			if (isPayed)
			{
				cmdText = "update [Invoice] SET Payed=1,PayedDate=@PayedDate WHERE Code=@InvoiceCode";
			}
			else
			{
				cmdText = "update [Invoice] SET Payed=0,PayedDate=null WHERE Code=@InvoiceCode";
			}
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.Parameters.Add(new SqlParameter("@PayedDate", SqlDbType.DateTime));
				this.updateCommand.Parameters["@PayedDate"].Value = sPayedDate;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataSet GetTypeSubtotalByCondition(InvoiceCondition ic)
		{
			DataSet dataSet = new DataSet("TypeSubtotal");
			string str = "SELECT pt.[Name] AS 类型,sum(ii.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,ii.Quantity)) AS 周长,p.Price as 价格 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*ii.Quantity) AS 数量,ts.unitPrice as 单价 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join Technics AS ts ON ts.ProductID=p.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			str = str + sqlCondition + " group by pt.[Name],p.Price order by pt.[Name]";
			text = text + sqlCondition + " group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataSet GetTypeSubtotalByCondition_2(InvoiceCondition ic)
		{
			DataSet dataSet = new DataSet("TypeSubtotal");
			string str = "SELECT pt.[Name] AS 类型,sum(ii.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,ii.Quantity)) AS 周长 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*ii.Quantity) AS 数量 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join Technics AS ts ON ts.ProductID=p.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			str = str + sqlCondition + " group by pt.[Name] order by pt.[Name]";
			text = text + sqlCondition + " group by ts.technicsName order by ts.technicsName";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataSet GetTypeSubtotalByCondition_3(InvoiceCondition ic)
		{
			string str = "SELECT c.CompanyName as 公司名称,pt.[Name] AS 类型,sum(ii.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 面积, sum(dbo.ItemMoney(p.PKID,ii.Quantity)) AS 金额 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT c.CompanyName as 公司名称,ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*ii.Quantity) AS 数量 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join Technics AS ts ON ts.ProductID=p.PKID";
			string text2 = " SELECT c.CompanyName as 公司名称,pt.[Name] AS 类型,sum(ii.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 面积, sum(dbo.ItemMoney(p.PKID,ii.Quantity)) AS 金额 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join Product AS p on ii.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			str = str + sqlCondition + " group by c.CompanyName,pt.[Name] order by c.CompanyName,pt.[Name]";
			text = text + sqlCondition + " group by c.CompanyName,ts.technicsName order by c.CompanyName,ts.technicsName";
			text2 = text2 + sqlCondition + " and i.Payed=0 group by c.CompanyName,pt.[Name] order by c.CompanyName,pt.[Name]";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text + text2;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataTable GetTypeSubtotalByCondition_4(InvoiceCondition ic)
		{
			string text = "select c.CompanyName AS 客户,sum(dbo.TotalValueOfInvoice(i.Code)) AS 金额 FROM [Invoice] AS i INNER JOIN Project AS pj ON i.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			text = text + sqlCondition + " group by c.CompanyName order by 金额";
			DataTable dataTable = new DataTable("CoustomerMoney");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool UpdateRemark(string sInvoiceCode, string sNewRemark)
		{
			string cmdText = "UPDATE Invoice SET Remark=@Remrk WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.Parameters["@Remrk"].Value = sNewRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataSet GetTypeSubtotalOfInvoice(string sInvoiceCode)
		{
			string str = "SELECT pt.[Name] AS 类型,sum(ii.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,ii.Quantity)) AS 周长,p.Price as 价格 FROM InvoiceItem as ii inner join Invoice as i on ii.InvoiceID=i.PKID inner join Product AS p on ii.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE i.Code=@InvoiceCode group by pt.[Name],p.Price";
			string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*ii.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID inner join InvoiceItem as ii on ii.ProductID=p.PKID inner join Invoice as i on ii.InvoiceID=i.PKID WHERE i.Code=@InvoiceCode group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public int GetPrintedQuantity(string sInvoiceCode)
		{
			string cmdText = "SELECT PrintedQuantity from [Invoice] where Code=@InvoiceCode";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnce(string sInvoiceCode)
		{
			string cmdText = "Update [Invoice] set PrintedQuantity=PrintedQuantity+1 where Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool LockData(string sInvoiceCode)
		{
			string cmdText = "UPDATE [Invoice] SET Locked=1 WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnLockData(string sInvoiceCode)
		{
			string cmdText = "UPDATE [Invoice] SET Locked=0 WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Activate(string sInvoiceCode)
		{
			string cmdText = "UPDATE [Invoice] SET Cancelled=0 WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Cancel(string sInvoiceCode)
		{
			string cmdText = "UPDATE [Invoice] SET Cancelled=1 WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable LoadInvoiceByInvoiceCode(string sInvoiceCode)
		{
			DataTable dataTable = new DataTable("Invoice");
			string cmdText = "SELECT i.PKID AS 发货单序号,i.Code AS 发货单号,c.CompanyName AS 公司名称,c.Linkman AS 公司联系人,pj.ProjectName AS 计划名称,pj.Linkman AS 计划联系人,pj.LinkmanPhone AS 计划联系人电话,pj.ProjectAddress AS 计划地址,i.InvoiceDate AS 发货日期,i.Locked AS 是否锁定,i.Cancelled AS 是否作废,i.Remark AS 发货单备注,op.[Name] AS 操作者 FROM [Invoice] AS i INNER JOIN Project AS pj ON i.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON i.OperatorID=op.PKID WHERE i.Code=@InvoiceCode";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable LoadInvoiceForUpdate(string sInvoiceCode)
		{
			DataTable dataTable = new DataTable("Invoice");
			string cmdText = "SELECT i.PKID,i.Code,c.PKID,c.CompanyName,pj.PKID,pj.ProjectName,pj.Linkman,i.InvoiceDate,i.Remark,op.[Name] FROM [Invoice] AS i INNER JOIN Project AS pj ON i.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON i.OperatorID=op.PKID WHERE i.Code=@InvoiceCode";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetInvoicesByCondition(InvoiceCondition ic)
		{
			DataTable dataTable = new DataTable("Invoice");
			string text = "SELECT i.PKID AS 序号,i.Code AS 发货单号,c.CompanyName AS 客户,pj.ProjectName AS 计划,i.InvoiceDate AS 发货日期,i.Locked AS 锁定数据,i.Remark AS 备注,op.Name AS 操作者,i.PrintedQuantity as 打印次数 FROM [Invoice] AS i INNER JOIN Project AS pj ON i.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON i.OperatorID=op.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			text = text + sqlCondition + " ORDER BY c.[CompanyName],i.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetInvoicesByProductID(string sProductID)
		{
			DataTable dataTable = new DataTable("Invoice");
			string cmdText = "SELECT i.PKID AS 序号,i.Code AS 发货单号,c.CompanyName AS 客户,pj.ProjectName AS 计划,i.InvoiceDate AS 发货日期,i.Locked AS 锁定数据,i.Remark AS 备注,op.Name AS 操作者 FROM [Invoice] AS i INNER JOIN Project AS pj ON i.ProjectID=pj.PKID inner join InvoiceItem as ii on ii.InvoiceID=i.PKID inner join Customer AS c ON pj.CustomerID=c.PKID inner join Operator AS op ON i.OperatorID=op.PKID where ii.ProductID=@ProductID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetSumUpByCondition(InvoiceCondition ic)
		{
			DataTable dataTable = new DataTable("SumUp");
			string text = "SELECT i.PKID AS 序号,i.Code AS 发货单号,c.CompanyName AS 客户,pj.ProjectName AS 计划, stuff( (select distinct '/'+convert(varchar(32),o.SpeakForDate,23) from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID inner join [Order] as o on p.OrderID=o.PKID where ii.InvoiceID=i.PKID for xml path(''))       ,1,1,'') as 订货日期,i.InvoiceDate AS '发货日期',dbo.AmountToInvoice(i.Code) AS 数量,dbo.TotalSquareOfInvoice(i.Code) AS 面积,dbo.TotalValueOfInvoice(i.Code) AS 金额,i.Payed as '付款状态',i.PayedDate as '付款时间',i.Locked AS '锁定',i.Cancelled AS 作废," + (LoginInfo.Role[PowerName.InvoiceData_SeePrice] ? "dbo.GetProductTypeOfInvoice_2(i.Code) as 类型" : "dbo.GetProductTypeOfInvoice_1(i.Code) as 类型") + ",i.Remark AS 备注,op.[Name] AS 操作者 from [Invoice] as i inner join Project as pj on i.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join operator as op on i.OperatorID=op.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(ic);
			text = text + sqlCondition + " ORDER BY c.[CompanyName],i.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public string InsertInvoice(string sInvoiceCode, int nProjectID, string sRemark, int nOperatorID)
		{
			string result = null;
			try
			{
				this.insertCommand = new SqlCommand("InsertInvoice", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@invoiceCode", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters["@invoiceCode"].Value = sInvoiceCode;
				this.insertCommand.Parameters["@invoiceCode"].Direction = ParameterDirection.InputOutput;
				this.insertCommand.Parameters.Add(new SqlParameter("@projectID", SqlDbType.Int));
				this.insertCommand.Parameters["@projectID"].Value = nProjectID;
				this.insertCommand.Parameters.Add(new SqlParameter("@remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@remark"].Value = sRemark;
				this.insertCommand.Parameters.Add(new SqlParameter("@operatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@operatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
				result = this.insertCommand.Parameters["@invoiceCode"].Value.ToString();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool Update(string sInvoiceCode, int nProjectID, string sRemark)
		{
			string cmdText = "UPDATE [Invoice] SET ProjectID=@ProjectID,Remark=@Remrk WHERE Code=@InvoiceCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				this.updateCommand.Parameters["@ProjectID"].Value = nProjectID;
				this.updateCommand.Parameters["@Remrk"].Value = sRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Delete(long nInvoiceID)
		{
			string cmdText = "DELETE [Invoice] where PKID=@InvoiceID";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@InvoiceID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@InvoiceID"].Value = nInvoiceID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		private string GetSqlCondition(InvoiceCondition ic)
		{
			string text = "";
			if (ic.CustomerID > -1)
			{
				if (text == "")
				{
					text += "pj.CustomerID=@CustomerID";
				}
				else
				{
					text += " AND pj.CustomerID=@CustomerID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.selectCommand.Parameters["@CustomerID"].Value = ic.CustomerID;
			}
			if (ic.ProjectID > -1)
			{
				if (text == "")
				{
					text += "i.ProjectID=@ProjectID";
				}
				else
				{
					text += " AND i.ProjectID=@ProjectID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.selectCommand.Parameters["@ProjectID"].Value = ic.ProjectID;
			}
			if (ic.InvoiceDateStart != null)
			{
				if (text == "")
				{
					text += "i.InvoiceDate>=CONVERT(DATETIME,@InvoiceDateStart) AND i.InvoiceDate<=CONVERT(DATETIME,@InvoiceDateEnd)";
				}
				else
				{
					text += " AND i.InvoiceDate>=CONVERT(DATETIME,@InvoiceDateStart) AND i.InvoiceDate<=CONVERT(DATETIME,@InvoiceDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceDateStart"].Value = ic.InvoiceDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceDateEnd"].Value = ic.InvoiceDateEnd;
			}
			if (ic.Locked > -1)
			{
				if (text == "")
				{
					text += "i.Locked=@Locked";
				}
				else
				{
					text += " AND i.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = ic.Locked;
			}
			if (ic.Cancelled > -1)
			{
				if (text == "")
				{
					text += "i.Cancelled=@Cancelled";
				}
				else
				{
					text += " AND i.Cancelled=@Cancelled";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Cancelled", SqlDbType.Bit));
				this.selectCommand.Parameters["@Cancelled"].Value = ic.Cancelled;
			}
			if (ic.InvoiceCode != null)
			{
				if (text == "")
				{
					text += "i.Code LIKE '%'+@InvoiceCode+'%'";
				}
				else
				{
					text += " AND i.Code LIKE '%'+@InvoiceCode+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = ic.InvoiceCode.Replace(" ", "%");
			}
			if (ic.Remark != null)
			{
				if (text == "")
				{
					text += "i.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND i.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = ic.Remark.Replace(" ", "%");
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			return text;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
